package cn.wit.sst.utils;

import java.io.File;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;

import javax.xml.parsers.ParserConfigurationException;

import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.openxml4j.opc.PackageAccess;
import org.apache.poi.ss.usermodel.DataFormatter;
import org.apache.poi.ss.util.CellAddress;
import org.apache.poi.ss.util.CellReference;
import org.apache.poi.util.SAXHelper;
import org.apache.poi.xssf.eventusermodel.ReadOnlySharedStringsTable;
import org.apache.poi.xssf.eventusermodel.XSSFReader;
import org.apache.poi.xssf.eventusermodel.XSSFSheetXMLHandler;
import org.apache.poi.xssf.eventusermodel.XSSFSheetXMLHandler.SheetContentsHandler;
import org.apache.poi.xssf.model.StylesTable;
import org.apache.poi.xssf.usermodel.XSSFComment;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.xml.sax.InputSource;
import org.xml.sax.SAXException;
import org.xml.sax.XMLReader;

import cn.wit.sst.domain.Raw;

public class StreamingDataLoader {
	private static final int STU_NO 		= 0; // 学号
	private static final int STU_NAME 	= 1; // 姓名
	private static final int COLLEGE 		= 2; // 学院
	private static final int CLAZZ 		= 3; // 班级
	private static final int CREDIT		= 4; // 学分
	private static final int COURSE_TYPE	= 5; // 课程性质
	private static final int COURSE_NO 	= 6; // 选课课号
	private static final int COURSE_NAME	= 7; // 课程名称
	private static final int SCORE_ORD	= 8; // 平时成绩
	private static final int SCORE_MID  	= 9; // 期中成绩
	private static final int SCORE_END	= 10; // 期末成绩
	private static final int SCORE_EXP 	= 11; // 实验成绩
	private static final int SCORE_TOTAL	= 12; // 总评成绩
	private static final int SCORE_AMO	= 13; // 折算成绩
	private static final int SCORE_SUP 	= 14; // 补考成绩
	private static final int RETAKE_MARK 	= 15; // 重修标记
	private static final int SCORE_RET	= 16; // 重修成绩
	private static final int REMARK		= 17; // 备注
	private static final int GRADE		= 18; // 学年
	private static final int SEMESTER		= 19; // 学期
	private static final int GPA			= 20; // 绩点

	private static Logger logger = LoggerFactory.getLogger(StreamingDataLoader.class);

	public static List<Raw> loadData(String file) throws Exception {
		List<Raw> data = new ArrayList<>();

		try (OPCPackage p = OPCPackage.open(new File(file).getPath(), PackageAccess.READ)) {
			ReadOnlySharedStringsTable strings = new ReadOnlySharedStringsTable(p);
			XSSFReader xssfReader = new XSSFReader(p);
			StylesTable styles = xssfReader.getStylesTable();

			// 遍历每个sheet
			XSSFReader.SheetIterator iter = (XSSFReader.SheetIterator) xssfReader.getSheetsData();
			int index = 0;
			while (iter.hasNext()) {
				try (InputStream stream = iter.next()) {
					String sheetName = iter.getSheetName();
					if (index != 0) {	// TODO 第一个sheet数据格式和其它sheet不一致，先略过
						logger.info("开始解析第{}个sheet：{}", index + 1, sheetName);

						// 处理当前sheet
						List<Raw> items = processSheet(styles, strings, stream);
						data.addAll(items);

						logger.info("解析完毕。共{}条数据。", items.size());
					}
				}
				++index;
			}

			logMemory();
		}

		return data;
	}

	public static List<Raw> processSheet(StylesTable styles, ReadOnlySharedStringsTable strings,
			InputStream sheetInputStream) throws IOException, SAXException {
		DataFormatter formatter = new DataFormatter();
		InputSource sheetSource = new InputSource(sheetInputStream);
		try {
			XMLReader sheetParser = SAXHelper.newXMLReader();
			CustomSheetHanlder sheetHandler = new CustomSheetHanlder();
			sheetParser.setContentHandler(new XSSFSheetXMLHandler(styles, null, strings, sheetHandler, formatter, false));
			sheetParser.parse(sheetSource);

			return sheetHandler.getItems();
		} catch (ParserConfigurationException e) {
			throw new RuntimeException("SAX parser appears to be broken - " + e.getMessage());
		}
	}

	private static class CustomSheetHanlder implements SheetContentsHandler {
		List<Raw> items = new ArrayList<>();

		private boolean firstCellOfRow;
		private int currentRow = -1;
		private int currentCol = -1;
		private Raw raw;	// 当前行数据

		public List<Raw> getItems() {
			return items;
		}

		@Override
		public void startRow(int rowNum) {
			firstCellOfRow = true;
			currentRow = rowNum;
			currentCol = -1;

			if (rowNum != 0) {	// 跳过表头
				raw = new Raw();
			}
		}

		@Override
		public void endRow(int rowNum) {
			if (rowNum != 0) {
				items.add(raw);
			}
		}

		@Override
		public void cell(String cellReference, String formattedValue, XSSFComment comment) {
			if (currentRow == 0) return;

			if (firstCellOfRow) {
				firstCellOfRow = false;
			}

			// gracefully handle missing CellRef here in a similar way as
			// XSSFCell does
			if (cellReference == null) {
				cellReference = new CellAddress(currentRow, currentCol).formatAsString();
			}

			// Did we miss any cells?
			int thisCol = (new CellReference(cellReference)).getCol();
			currentCol = thisCol;

			// 解析当前行
			parseRow(thisCol, formattedValue);
		}

		@Override
		public void headerFooter(String arg0, boolean arg1, String arg2) {
			System.out.println(arg0 + "/" + arg1 + "/" + arg2);
		}


		private void parseRow(int column, String data) {
			switch(column) {
			case STU_NO:
				raw.setSno(Long.valueOf(data)); break;
			case STU_NAME:
				raw.setName(data); break;
			case COLLEGE:
				raw.setCollege(data); break;
			case CLAZZ:
				raw.setClazz(data); break;
			case CREDIT:
				raw.setCredit(Float.valueOf(data)); break;
			case COURSE_TYPE:
				raw.setCourseType(data); break;
			case COURSE_NAME:
				raw.setCourseName(data); break;
			case COURSE_NO:
				raw.setCourseNo(data); break;
			case SCORE_ORD:
				raw.setScoreOrd(Float.valueOf(data)); break;
			case SCORE_MID:
				raw.setScoreMid(Float.valueOf(data)); break;
			case SCORE_END:
				raw.setScoreEnd(Float.valueOf(data)); break;
			case SCORE_EXP:
				raw.setScoreExp(Float.valueOf(data)); break;
			case SCORE_TOTAL:
				// 此处分数可能为‘作弊’字符串
				String scoreTotal = data;
				if (isNumeric(scoreTotal)) {
					raw.setScoreTotal(toFloat(scoreTotal));
				}
				break;
			case SCORE_AMO:
				raw.setScoreAmo(Float.valueOf(data)); break;
			case SCORE_SUP:
				raw.setScoreSup(Float.valueOf(data)); break;
			case SCORE_RET:
				// 此处分数可能为‘旷考’字符串！
				String scoreRet = data;
				if (isNumeric(scoreRet)) {
					raw.setScoreRet(toFloat(scoreRet));
				}
				break;
			case RETAKE_MARK:
				raw.setRetakeMark(data); break;
			case REMARK:
				raw.setRemark(data); break;
			case GRADE:
				raw.setGrade(data); break;
			case SEMESTER:
				raw.setSemester(Byte.valueOf(data)); break;
			case GPA:
				raw.setGpa(Float.valueOf(data)); break;
			}
		}
	}

	private static boolean isNumeric(String str) {
		for (int i = 0; i < str.length(); i++) {
			if (!Character.isDigit(str.charAt(i))) {
				return false;
			}
		}

		return true;
	}

	private static Float toFloat(String s) {
		if (s == null || s.length() == 0) {
			return null;
		}

		return Float.valueOf(s);
	}

	public static void logMemory() {
		logger.info("总内存：{}，已占有内存：{}，空闲内存：{}，最大内存：{}",
			formatSize(Runtime.getRuntime().totalMemory()),
			formatSize(Runtime.getRuntime().totalMemory() - Runtime.getRuntime().freeMemory()),
			formatSize(Runtime.getRuntime().freeMemory()),
			formatSize(Runtime.getRuntime().maxMemory())
		);
	}

	private static String formatSize(Long size) {
		return size / 1024 / 1024 + "M";
	}
}